﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BusinessLibrary.BLL;
using System.Data.SqlClient;

namespace BusinessLibrary.DAL
{
    class sqlVille
    {
        public static Ville ReaderToObject(SqlDataReader dr)
        {
            Ville _ville = new Ville();
            _ville.Id = int.Parse(dr["Id"].ToString());

            _ville.Nom = dr["Nom"].ToString();
            _ville.Nom = dr["Distance"].ToString();


            if (!object.ReferenceEquals(dr["Id_gouvernorat"], DBNull.Value))
            {
                _ville.gouvernorat = Gouvernorat.GetGouvernoratById(int.Parse(dr["Id_gouvernorat"].ToString()));
            }
            return _ville;
        }

        public static Ville GetVilleById(int id)
        {
            Ville ville = new Ville();
            string strSQL = @"select * from Ville   where UserId=@Id";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);
                    cmd.Parameters.AddWithValue("@Id", id);
                    SqlDataReader dr = cmd.ExecuteReader();

                    while (dr.Read())
                    {
                        ville = ReaderToObject(dr);
                    }
                    dr.Close();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return ville;
        }

        public static int insertVille(Ville v)
        {
            int n = 0;
            string strSQL = @"insert into Ville (Nom,Distance,Id_gouvernorat) values(@nom,@distance, @id_gouvernorat)";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);

                    cmd.Parameters.AddWithValue("@nom", v.Nom);
                    cmd.Parameters.AddWithValue("@distance", v.Distance);
                    cmd.Parameters.AddWithValue("@id_gouvernorat", v.gouvernorat.Id);

                    n = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="s"></param>
        /// <returns></returns>
        public static int updateVille(Ville v)
        {
            int n = 0;

            string strSQl = @"UPDATE Ville SET (@nom IS NULL OR Nom=@nom), (@distance IS NULL OR Distance=@distance) Id_gouvernorat=@id_gouvernorat where Id=@id ";
            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();

                    SqlCommand cmd = new SqlCommand(strSQl, cnx);

                    cmd.Parameters.AddWithValue("@nom", v.Nom);
                    cmd.Parameters.AddWithValue("@id_gouvernorat", v.gouvernorat.Id);
                    n = cmd.ExecuteNonQuery();

                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }



        /// <summary>
        /// 
        /// </summary>
        /// <param name="id"></param>
        /// <param name="idAuthentif"></param>
        /// <returns></returns>
        public static int deletVille(int id)
        {
            int n = 0;
            string strSQL = @"delete from Ville where id=@id)";

            try
            {
                using (SqlConnection cnx = new SqlConnection(DALtools.DataConnectionString))
                {
                    cnx.Open();
                    SqlCommand cmd = new SqlCommand(strSQL, cnx);

                    cmd.Parameters.AddWithValue("@id", id);


                    n = cmd.ExecuteNonQuery();
                    cnx.Close();
                }
            }
            catch (Exception) { }
            return n;
        }
    }
}